﻿------------- danh sach lich he thong theo Thang -----------

CREATE
--ALTER
PROC proc_DanhSachTheoThang
@thang int,@nam int
AS
BEGIN
	SELECT * FROM LICHHT WHERE MONTH(NgayLenLich) = @thang AND YEAR(NgayLenLich)=@nam
END
GO
exec proc_DanhSachTheoThang 10,2013

------------------ kiểm tra mon an da len lịch trong thang chua ----------
CREATE
--ALTER
PROC proc_MonAnChuaLenLich
@mama varchar(6), @thang int, @nam int, @res int output
AS
BEGIN TRAN
	IF(@mama not in (Select MaMA From LICHHT where  MONTH(NgayLenLich) = @thang AND YEAR(NgayLenLich)=@nam ))
	BEGIN
		set @res = -1
	END
	ELSE
	BEGIN
		set @res = 1
		END

COMMIT TRAN
GO
Declare @res int
EXEC proc_MonAnChuaLenLich 'MA029',10,2013,@res output
print @res

select * from LICHHT

---------- kiểm tra đã lên lịch cho tháng đó chưa -----------
CREATE
--ALTER
PROC proc_KiemTraLenLich
@thang int, @nam int, @res int output
AS
BEGIN TRAN
	IF(@thang in (Select MONTH(NgayLenLich) From LICHHT where  MONTH(NgayLenLich) = @thang AND YEAR(NgayLenLich)=@nam ))
		set @res = 1
	ELSE
		set @res = 0
COMMIT TRAN
GO
Declare @res int
EXEC proc_KiemTraLenLich 9,2013,@res output
print @res


------------------------------            thêm món ăn vào hệ thống               ---------------------------------
CREATE 
--ALTER 
PROC proc_ThemMonAnHT
@mama varchar(6),@ngayll Datetime,@res int output
AS
BEGIN TRAN	
	BEGIN TRY
		IF(@mama not in(select MaMA From LICHHT where DATEDIFF(DD,NgayLenLich,@ngayll)=0 and DATEDIFF(YYYY,NgayLenLich,@ngayll)=0))
		BEGIN		
			Insert into LICHHT(MaMA,NgayLenLich) values (@mama,@ngayll)
			set @res =1;
		END
		ELSE
		BEGIN
			set @res =0;
		END
	END TRY
	BEGIN CATCH
		RAISERROR (N'LỖI HỆ THỐNG',16,1)
		ROLLBACK TRAN
		RETURN
	END CATCH

COMMIT TRAN
GO

Declare @res int
EXEC proc_ThemMonAnHT 'MA001','10/10/2013',@res output  -- thang /ngay/nam
print @res


select * from Monan
------ lay so luong loai mon an -----------------------
CREATE
--ALTER
PROC proc_SoLuongLoaiMonAn
@malma varchar(6),@ngayll Datetime,@res int output
AS

BEGIN 
	set @res=(select COUNT(ma.MaLoaiMA)
	From LICHHT ht, MONAN ma
	where ht.MaMA=ma.MaMA and DATEDIFF(DD,ht.NgayLenLich,@ngayll)=0 and DATEDIFF(YYYY,ht.NgayLenLich,@ngayll)=0 and ma.MaLoaiMA=@malma)
END
GO

Declare @res int
EXEC proc_SoLuongLoaiMonAn 'LM002','2013/10/09',@res output  -- thang /ngay/nam
print @res



CREATE
--ALTER
PROC proc_SoLuongLoaiMonAn
@thang int,@nam int, @sl int output
AS
BEGIN
	set @sl=(Select COUNT(*) FROM LICHHT where MONTH(NgayLenLich)=@thang AND YEAR(NgayLenLich)=@nam)
	print @sl
END

Declare @sl int
exec proc_SoLuongLoaiMonAn 10,2013,@sl output
print(@sl)